<?php
/**
 * Created by PhpStorm.
 * User: HuangLuSen
 * Date: 2017/12/27
 * Time: 16:24
 */

namespace app\admin\logic;


use app\admin\model\AuthGroup;
use app\admin\model\Excel as Excel_Model;
use nx\Data;
use think\Cache;
use think\Loader;
use think\Queue;
use PHPExcel;
use PHPExcel_Reader_Excel2007;
use PHPExcel_Writer_Excel5;
class Excel
{
    use \app\common\traits\Common;
    use \app\common\traits\Gate;
    /**
     * 导入客资
     * @param $file_name
     * @return array
     * @author: Huang
     */
    public static function daorukezi($file_name)
    {

        if (!empty($file_name)) {
            $objPHPExcel = daoruexcel($file_name);
            if (is_array($objPHPExcel)) {
                return $objPHPExcel;
            }
            $sheet = $objPHPExcel->getSheet(0);
            $highestRow = $sheet->getHighestRow(); // 取得总行数
//            $highestColumn = $sheet->getHighestColumn(); // 取得总列数

//            $titleJ = (string)$sheet->getCell("J" . 1)->getValue();
//            $titleK = (string)$sheet->getCell("K" . 1)->getValue();
//            $titleL = (string)$sheet->getCell("L" . 1)->getValue();
            $laiyuan = Origin::OriginsToInt();  //转换来源
            $qudao = Qudao::SourcesToInt();  //转换渠道
            for ($i = 2; $i <= $highestRow; $i++) {
                $arr['name_z'] = trim((string)$sheet->getCell("A" . $i)->getValue());
                $arr['name_f'] = trim((string)$sheet->getCell("B" . $i)->getValue());
                $arr['phone_z'] = trim((string)$sheet->getCell("C" . $i)->getValue());
                $arr['phone_f'] = trim((string)$sheet->getCell("D" . $i)->getValue());
                $arr['wechat'] = trim((string)$sheet->getCell("E" . $i)->getValue());
                $arr['qq'] = trim((string)$sheet->getCell("F" . $i)->getValue());
                $arr['bbname'] = trim((string)$sheet->getCell("G" . $i)->getValue());
                $arr['bbage'] = trim((string)$sheet->getCell("I" . $i)->getValue());
                $arr['gender_b'] = self::getCommonModel()->SexToint()[(string)$sheet->getCell("J" . $i)->getValue()];
                $arr['channel'] = trim((string)$sheet->getCell("K" . $i)->getValue());
                $arr['source'] = trim((string)$sheet->getCell("L" . $i)->getValue());
//                $arr['types'] = self::getCommonModel()->TypesToint()[(string)$sheet->getCell("G" . $i)->getValue()];
//                $arr['invitation_group'] = \app\admin\model\Order::getInvitationGroupToint((string)$sheet->getCell("H" . $i)->getValue());
                $arr['shot_mode'] = self::getCommonModel()->ShotModeToint()[(string)$sheet->getCell("M" . $i)->getValue()];

                $arr['qdbz'] = (string)$sheet->getCell("N" . $i)->getValue();
                $arr['channel'] = $qudao["{$arr['channel']}"];
                $arr['source'] = $laiyuan["{$arr['source']}"];
                $a = new Excel();
                $user = session('admin_user');
                $line = '';
                if ($a->hasRole(['qdjl', 'qdzg', 'qdzy', 'qdkf'])) {
                    if ($a->hasRole('line', $user['adminid'])) {
                        $line = true;
                    } else {
                        $line = false;
                    }
                } elseif ($a->hasRole(['yyjl', 'yyzg', 'yyzy'])) {
                    if ($a->hasRole('line', $user['adminid'])) {
                        $line = true;
                    } else {
                        $line = false;
                    }
                }
                if ($line === '') {
                    $arr['banji'] = trim((string)$sheet->getCell("O" . $i)->getValue());
                    $arr['xingqi'] = trim((string)$sheet->getCell("P" . $i)->getValue());
                    $arr['shijianduan'] = trim((string)$sheet->getCell("Q" . $i)->getValue());
                }
                if ($line==true){
                    $arr['banji'] = trim((string)$sheet->getCell("O" . $i)->getValue());
                    $arr['xingqi'] = trim((string)$sheet->getCell("P" . $i)->getValue());
                    $arr['shijianduan'] = trim((string)$sheet->getCell("Q" . $i)->getValue());
                }else{

                }
                $bmtime = $sheet->getCell("H" . $i)->getValue();

                if (empty($bmtime)) {
                    $arr['bbcstime'] = $bmtime;
                } else {
                    $arr['bbcstime'] = strtotime(gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($bmtime)));
                }
//

//                $bmtime = strtotime($bmtime);
//                if( $bmtime < 0 || !$bmtime ){
//                    $bmtime = (string)$sheet->getCell("I" . $i)->getValue();
////                    $bmtime = $this->changeTime($bmtime);
//                }
//                $arr['otherbz'] = $titleJ . ":" . $sheet->getCell("J" . $i)->getValue() . ";" .
//                    $titleK . ":" . $sheet->getCell("K" . $i)->getValue() . ";" .
//                    $titleL . ":" . $sheet->getCell("L" . $i)->getValue() . ";";

                $arr['jd_depar'] = is_administrator() ? 1 : $user['depar_id'];
                $arr['jdry'] = $user['adminid'];
               if($arr['name_z']!=''){
//                   $validate = Loader::validate('order');
//                   $a = $validate->check($arr);

                   $res = Queue::push('app\queue\jobs\Excel', $arr, $queue = null);
               }
////                //如果有失败
//                if (!$res) {
//                    return ['code' => 0, 'msg' => '导入失败，等待一会儿核对数据再导入,错误行数：' . $i . ',错误电话：' . $arr['phone']];
//                }
            }

            return ['code' => 1, 'msg' => '导入成功，等待后续服务器处理'];
        } else {
            return ['code' => 0, 'msg' => '错误'];
        }
    }

    /**
     * 导入爆破客资
     * @param $file_name
     * @return array
     * @author: Huang
     */
    public static function daoruBopokezi($file_name)
    {
        if (!empty($file_name)) {
            $objPHPExcel = daoruexcel($file_name);
            if (is_array($objPHPExcel)) {
                return $objPHPExcel;
            }
            $sheet = $objPHPExcel->getSheet(0);
            $highestRow = $sheet->getHighestRow(); // 取得总行数
            for ($i = 2; $i <= $highestRow; $i++) {
                $arr['laiyuan'] = trim((string)$sheet->getCell("A" . $i)->getValue());
                $arr['yplx'] = trim((string)$sheet->getCell("B" . $i)->getValue());
                $arr['name'] = trim((string)$sheet->getCell("C" . $i)->getValue());
                $arr['phone'] = trim((string)$sheet->getCell("D" . $i)->getValue());
               if($arr['name']!=''){
                   Queue::later(2,'app\job\job\Excel@baopo', $arr, 'sendwechattemp');
               }
////                //如果有失败
//                if (!$res) {
//                    return ['code' => 0, 'msg' => '导入失败，等待一会儿核对数据再导入,错误行数：' . $i . ',错误电话：' . $arr['phone']];
//                }
            }

//            return $arra;
            return ['code' => 1, 'msg' => '导入成功，等待后续服务器处理'];
        } else {
            return ['code' => 0, 'msg' => '错误'];
        }
    }

    /**
     * 导入数据到队列中
     * @param $file_name
     * @return array
     */
    public function daoruDataToQueue($file_name)
    {
        $userid = session('admin_user.adminid');
        $depar_id = session('admin_user.depar_id');
        if (is_file($file_name)) {
            $exceldata = read($file_name);//读取文件
            unset($exceldata[1]);
            foreach ($exceldata as $k=>$v) {
                $data['name_z'] = $v[1];//顾客姓名
                $data['name_f'] = $v[2];
                $data['phone_z'] = $v[3];
                $data['phone_f'] = $v[4];
                $data['bbname'] = $v[5];
                $data['daoru'] = 2;
                $data['jdtime'] = time();
                $data['cztime'] = time();
                $data['jdry'] = $userid;
                $data['jd_depar'] = $depar_id;
                $validate = Loader::validate('order');
                $a = $validate->check($data);
                if($a){
                    $map[$k]['name_z'] = $v[1];
                    $map[$k]['name_f'] = $v[2];
                    $map[$k]['phone_z'] = $v[3];
                    $map[$k]['phone_f'] = $v[4];
                    $map[$k]['bbname'] = $v[5];
                    $map[$k]['daoru'] = 2;
                    $map[$k]['jdtime'] = time();
                    $map[$k]['cztime'] = time();
                    $map[$k]['jdry'] = $userid;
                    $map[$k]['jd_depar'] = $depar_id;

                }else{
                    $arr[$k]['name_z'] = $v[1];
                    $arr[$k]['name_f'] = $v[2];
                    $arr[$k]['phone_z'] = $v[3];
                    $arr[$k]['phone_f'] = $v[4];
                    $arr[$k]['bbname'] = $v[5];
                    $arr[$k]['daoru'] = 2;
                    $arr[$k]['jdtime'] = time();
                    $arr[$k]['cztime'] = time();
                    $arr[$k]['jdry'] = $userid;
                    $arr[$k]['jd_depar'] = $depar_id;
                    $arr[$k]['errorinfo'] = $validate->getError();
                }
            }
            $aaaa = \think\Queue::push('app\queue\jobs\QueueClient@daoruOrderJob', $map, $queue = null);
            $res = Db::name('order_error')->insertAll($arr);
            if($res){
                return $arr;
            }
        } else {
            $this->error('请选择要上传的文件！');
        }
    }

    /**
     * 导出excl
     */

    public function exportexcel($data = array(), $title = array(), $filename = '纷乐迪电商管理系统')
    {
        /* 实例化类 */
        $objPHPExcel = new PHPExcel();

        /* 设置输出的excel文件为2007兼容格式 */
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);//非2007格式
        //$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        /* 设置当前的sheet */
        $objPHPExcel->setActiveSheetIndex(0);

        $objActSheet = $objPHPExcel->getActiveSheet();

        /*设置宽度*/
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(11);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(10);
        /* sheet标题 */
        $objActSheet->setTitle($filename);

        //设置表头
        $key = ord("A");
        $key2 = ord("@");//@--64
        foreach ($title as $v) {
//                $colum = chr($key);
            if ($key > ord("Z")) {
                $key2 += 1;
                $key = ord("A");
                $colum = chr($key2) . chr($key);//超过26个字母时才会启用
            } else {
                if ($key2 >= ord("A")) {
                    $colum = chr($key2) . chr($key);//超过26个字母时才会启用
                } else {
                    $colum = chr($key);
                }
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            $key += 1;
        }

        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach ($data as $key => $rows) { //行写入
            $span = ord("A");
            $span2 = ord("@");//@--64
            foreach ($rows as $keyName => $value) {// 列写入
//                    $j = chr($span);
                if ($span > ord("Z")) {
                    $span2 += 1;
                    $span = ord("A");
                    $j = chr($span2) . chr($span);//超过26个字母时才会启用
                } else {
                    if ($span2 >= ord("A")) {
                        $j = chr($span2) . chr($span);//超过26个字母时才会启用
                    } else {
                        $j = chr($span);
                    }
                }
                $objActSheet->setCellValue($j . $column, $value);
                $span++;
            }
            $column++;
        }

        /* 生成到浏览器，提供下载 */
        ob_end_clean();  //清空缓存
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="' . $filename . '.xls"');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

    public $Excel_model = '';
    /**获取模型
     * @return Classify_model|string
     */
    public function getModel()
    {
        if (empty($this->Excel_model)) {
            $this->Excel_model = new Excel_Model();
        }
        return $this->Excel_model;
    }

    /**将所有类别进行树形排列
     * @return array|false|mixed|\PDOStatement|string|\think\Collection
     */
    public function getAllExcelToTree()
    {
        $Excels = $this->getAllExcels();
        $Excels = Data::tree($Excels, 'name_z', 'name_f', 'phone_z', 'phone_f', 'bbname');
        return $Excels;
    }


    /**获得所有的类别类别（从缓存中）
     * @return false|mixed|\PDOStatement|string|\think\Collection   返回数组
     */
    public function getAllExcels()
    {
        $Excels = Cache::get('allExcels');
        if (!empty($Excels)) return $Excels;
        $Excels = $this->getModel()->select();
        if (empty($Excels)) return $Excels;
        $Excels = collection($Excels)->append(['status_text'])->toArray();
        Cache::set('allExcels', $Excels);
        return $Excels;
    }

    public function delExcel($id)
    {
        if (empty($id)) {
            return ['result' => false, 'msg' => '请选择要操作的数据'];
        }
        if (Excel_model::get($id)) {
            if (Excel_model::destroy($id)) {
                Cache::rm('allExcels');
                return ['result' => true, 'msg' => '删除成功'];
            } else {
                return ['result' => false, 'msg' => '删除失败'];
            }
        } else {
            return ['result' => false, 'msg' => '要删除的数据不存在'];
        }
    }


}